﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class CuonSachDAO
    {
        #region Them
        public static bool ThemCuonSach(CuonSachDTO sach)
        {
            bool result = false;
            try
            {
                // create list sqlparameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaSach", sach.MaSach));
                sqlParams.Add(new SqlParameter("@MaLanXB", sach.MaLanXB));
                sqlParams.Add(new SqlParameter("@SoThuTu", sach.SoThuTu));
                sqlParams.Add(new SqlParameter("@MaTuaDe", sach.MaTuaDe));
                sqlParams.Add(new SqlParameter("@TinhTrang", sach.TinhTrang));
                sqlParams.Add(new SqlParameter("@NgayNhap", sach.NgayNhap));
                sqlParams.Add(new SqlParameter("@Lent", sach.Lent));

                // call stored procedure :))
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_InsertCUONSACH", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #region Xoa
        public static bool XoaCuonSach(String masach)
        {
            bool result = false;
            try
            {
                // create list sqlparameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaSach", masach));

                // call stored procedure :))
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_DeleteCUONSACH", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #region Cap nhap
        public static bool SuaCuonSach(CuonSachDTO sach)
        {
            bool result = false;
            try
            {
                // create list sqlparameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaSach", sach.MaSach));
                sqlParams.Add(new SqlParameter("@MaLanXB", sach.MaLanXB));
                sqlParams.Add(new SqlParameter("@SoThuTu", sach.SoThuTu));
                sqlParams.Add(new SqlParameter("@MaTuaDe", sach.MaTuaDe));
                sqlParams.Add(new SqlParameter("@TinhTrang", sach.TinhTrang));
                sqlParams.Add(new SqlParameter("@NgayNhap", sach.NgayNhap));
                sqlParams.Add(new SqlParameter("@Lent", sach.Lent));

                // call stored procedure :))
                int n = SqlDataAccessHelper.ExecuteNoneQuery("usp_UpdateCUONSACH", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        #endregion
        #region Tim kiem
        public static CuonSachDTO TimCuonSachBangID(String masach)
        {
            CuonSachDTO result = new CuonSachDTO();
            try
            {
                // create list sqlparameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaSach", masach));

                // call stored procedure
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCUONSACH", sqlParams);
                DataRow dr = dt.Rows[0];

                result.MaSach       = dr["MaSach"].ToString();
                result.MaLanXB      = SqlDataAccessHelper.ParseByte(dr["MaLanXB"].ToString());
                result.SoThuTu      = SqlDataAccessHelper.ParseInt(dr["SoThuTu"].ToString());
                result.MaTuaDe      = dr["MaTuaDe"].ToString();
                result.TinhTrang    = dr["TinhTrang"].ToString();
                result.NgayNhap     = DateTime.Parse(dr["NgayNhap"].ToString());
                result.Lent         = dr["Lent"].ToString();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public static DataTable TimTatCaCuonSach()
        {
            DataTable dt= new DataTable();
            try
            {
                // call stored procedure
                 dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCUONSACHesAll");
                
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return dt;
        }
        public static List<CuonSachDTO> TimCuonSachBangTuaDeVaLanXB(String matuade, byte malanxb)
        {
            List<CuonSachDTO> result = new List<CuonSachDTO>();
            try
            {
                // create list sqlparameters
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaLanXB", malanxb));
                sqlParams.Add(new SqlParameter("@MaTuaDe", matuade));
                // call stored procedure
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCUONSACHesByAndMaLanXBMaTuaDe", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    CuonSachDTO tmp = new CuonSachDTO();

                    tmp.MaSach      = dr["MaSach"].ToString();
                    tmp.MaLanXB     = byte.Parse(dr["MaLanXB"].ToString());
                    tmp.SoThuTu     = int.Parse(dr["SoThuTu"].ToString());
                    tmp.MaTuaDe     = dr["MaTuaDe"].ToString();
                    tmp.TinhTrang   = dr["TinhTrang"].ToString();
                    tmp.NgayNhap    = DateTime.Parse(dr["NgayNhap"].ToString());
                    tmp.Lent        = dr["Lent"].ToString();

                    result.Add(tmp);
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public static List<CuonSachDTO> TimCuonSachBangTuaDe(int matuade)
        {
            List<CuonSachDTO> result = new List<CuonSachDTO>();
            try
            {
                // create list sqlparameters
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaTuaDe", matuade));
                // call stored procedure
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCUONSACHesByAndMaTuaDe", sqlParams);
                foreach (DataRow dr in dt.Rows)
                {
                    CuonSachDTO tmp = new CuonSachDTO();

                    tmp.MaSach      = dr["MaSach"].ToString();
                    tmp.MaLanXB     = byte.Parse(dr["MaLanXB"].ToString());
                    tmp.SoThuTu     = int.Parse(dr["SoThuTu"].ToString());
                    tmp.MaTuaDe     = dr["MaTuaDe"].ToString();
                    tmp.TinhTrang   = dr["TinhTrang"].ToString();
                    tmp.NgayNhap    = DateTime.Parse(dr["NgayNhap"].ToString());
                    tmp.Lent        = dr["Lent"].ToString();

                    result.Add(tmp);
                }
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }
        public static List<CuonSachDTO> TimSachChuaMuon(){
            List<CuonSachDTO> result = new List<CuonSachDTO>();
            try
            {
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("TimCuonSachChuaMuon");
                foreach(DataRow dr in dt.Rows)
                {
                    CuonSachDTO tmp = new CuonSachDTO();

                    tmp.MaSach = dr["MaSach"].ToString();
                    tmp.MaTuaDe = dr["MaTuaDe"].ToString();
                    tmp.MaLanXB = byte.Parse(dr["MaLanXB"].ToString());
                    tmp.SoThuTu = int.Parse(dr["SoThuTu"].ToString());
                    tmp.NgayNhap = DateTime.Parse(dr["NgayNhap"].ToString());
                    tmp.TinhTrang = dr["TinhTrang"].ToString();
                    tmp.Lent = dr["Lent"].ToString();

                    result.Add(tmp);
                }
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return result;
        }
        public static DataTable TimSachDaMuon()
        {
            DataTable dt = new DataTable();
            try
            {
                dt = SqlDataAccessHelper.ExecuteQuery("TimCuonSachDaMuon");
            }
            catch (Exception)
            {
                               
            }
            return dt;
        }
        public static DataTable TimMaSachDaMuon(){
            DataTable dt = null;
            try
            {
                dt = SqlDataAccessHelper.ExecuteQuery("TimMaSachDaMuon");
            }
            catch (System.Exception ex)
            {
            	
            }
            return dt;
        }
        public static String TimTenSachTuMaSach(String masach)
        {
            string tensach = "";
            try
            {
                List<SqlParameter> sqlparams = new List<SqlParameter>();
                sqlparams.Add(new SqlParameter("@masach", masach));
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("TimTenSachTuMaSach", sqlparams);
                tensach = dt.Rows[0]["TenTuaDe"].ToString();
            }
            catch (System.Exception ex)
            {
            }
            return tensach;
        }
        public static bool CheckCuonSachBangID(String masach)
        {
            bool result = false;
            try
            {
                // create list sqlparameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MaSach", masach));

                // call stored procedure
                DataTable dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCUONSACH", sqlParams);
                if (dt.Rows.Count > 0)
                    result = true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static DataTable DanhSachCuonSach(){
            DataTable result = new DataTable();
            try
            {
                result = SqlDataAccessHelper.ExecuteQuery("DanhSachCuonSach");
            }
            catch (System.Exception ex)
            {
            	
            }
            return result;
        }
        #endregion
        #region Check va Hien Thi
        public static bool CheckSachDaMuon(String masach)
        {
            bool result = false;
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@MaSach", masach));
            DataTable dt = new DataTable();
            try
            {
                dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCUONSACHDAMUON", sqlParams);
                if (dt.Rows.Count > 0)
                {
                    result = true;
                }
            }
            catch (Exception)
            {

            }
            return result;
        }
        public static bool CheckSachDaTra(String masach)
        {
            bool result = false;
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@MaSach", masach));
            DataTable dt = new DataTable();
            try
            {
                dt = SqlDataAccessHelper.ExecuteQuery("usp_SelectCUONSACHDATRA", sqlParams);
                if (dt.Rows.Count > 0)
                {
                    result = true;
                }
            }
            catch (Exception)
            {

            }
            return result;
        }
        public static DataTable HienThiSachMuon(String MaPM)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@MaPM", MaPM));
            DataTable dt = new DataTable();
            try
            {
                dt = SqlDataAccessHelper.ExecuteQuery("usp_ViewSachMuon", sqlParams);
            }
            catch (Exception)
            {

            }
            return dt;
        }
        public static DataTable HienThiSachTra(String MaPT)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            sqlParams.Add(new SqlParameter("@MaPT", MaPT));
            DataTable dt = new DataTable();
            try
            {
                dt = SqlDataAccessHelper.ExecuteQuery("usp_ViewSachTra1", sqlParams);
            }
            catch (Exception)
            {

            }
            return dt;
        }
#endregion        
    }
}
